HW#1 --- last modified January 27 2019 04:55:13..

Solution set.

Due date: Sep 10

Files to be submitted:
  Hw1.zip

Purpose: To gain experience creating and altering a simple database. To learn about the relational model and the relational algebra. To make sure we can do simple Java coding necessary for this course.

The main course outcomes covered by this assignment are:

CLO2 -- Write SQL commands to create databases, create tables, insert/update/delete/retrieve rows in a common database management system.

Specification:

This assignment consists of two parts a written assignment and a coding assignment.

For the written assignment, do Exercise 2.2.1 and 2.2.2 out of the book. Then suggest an additional relation that might be useful as part of a banking database. Next install SQLite on your computer if you don't already have it. One easy way to install software that you will use from the command line is a package manager such as Apt (comes with Debian/Ubuntu Linux), Homebrew (Macos), or Chocolatey (Windows). From the command shell (Windows) or terminal (Linux/Macos), launch a SQLite shell. Use SQL CREATE TABLE statements as we did in class to create the bank database relations from exercise 2.2.1. Use INSERT statements to insert the rows of these tables. Make sure to make a transcript of each of the operations you perform and their results. Put your solution write-ups and this transcript in the file Problems.pdf and include it in the Hw1.zip that you submit for the homework.

For the coding part of the homework, I want you to show me you still remember Java and to get you thinking about what might be involved in programming even a tiny part of a DBMS system. One common query primitive implemented by most relational database systems is the so-called natural join of two tables/relations. For this part of the assignment you will write a Java program NaturalJoin.java to compute natural joins which will be compiled using a Java 10 compiler with the command from the command line (not a GUI):

javac NaturalJoin.java

Do not have an package statements in your program. Your program will then be run using a line of the form:

java NaturalJoin filename1.txt filename2.txt

For example, I might type:

java NaturalJoin Employee.txt Dept.txt

You can assume the filenames I run your program on exists and they contain text data in the following format:

1. The first row has the name of the fields used by the table, each in quotes, and delimited by tabs (\t), the end of the line delimited by a newline (\n).
2. The remaining rows each delimited by a new line, contains values for each of the fields in quotes and delimited by tabs.
3. You can assume no row is more than 32KB. You can assume a file has less than 500,000 rows.

For example, the file Employee.txt might have in it (using Wikipedia's example):

"Name"	"EmpId"	"DeptName"
"Harry"	"3415"	"Finance"
"Sally"	"2241"	"Sales"
"George"	"3401"	"Finance"
"Harriet"	"2202"	"Sales" 

and the file Dept.txt might have in it:

"DeptName"	"Manager"
"Finance"	"George"
"Sales"	"Harriet"
"Production"	"Charles" 

Your program given two such files should output using System.out a sequence of lines in the same format for the natural join of the relations/tables in the two files. It should not output anything else. So in the Employee.txt, Dept.txt example above, it should output:

"Name"	"EmpId"	"DeptName"	"Manager"
"Harry"	"3415"	"Finance"	"George"
"Sally"	"2241"	"Sales"	"Harriet"
"George"	"3401"	"Finance"	"George"
"Harriet"	"2202"	"Sales"	"Harriet" 

Submit your NaturalJoin.java also in the Hw1.zip file you submit for the homework.

Point Breakdown